Personal computers are everywhere, yet for most users of database applications it is still not easy to get the information they need and to manipulate that information to get the answers they need. This invention relates generally to database and computational applications and relates particularly to an improved system permitting users to retrieve information from databases and to create programs for manipulation of the data and for performing computations on the data.
One commonly used application is what is generically called a database application. With such an application, the user is able to enter data on a record-by-record basis, edit selected records, browse through the contents of the database, and print reports based on the contents of the database. Some database applications are called "relational", denoting that a field in a selected record of a first database can be a pointer into a second, related database. Some database applications permit the user to write a program in a specialized language peculiar to the application. One commonly used database application is dBase III, published by Ashton-Tate. Another in common contemporary use is Paradox, published by Borland. In dBase III, which is a relational database application, a user can run a program written in the dBase programming language; the conventionally used extension to the DOS filename for the dBase program is the extension of ".PRG". The PRG program is executed by means of the dBase application serving as an interpreter.
Another commonly used application is what is generically called a spreadsheet application. With such an application, the user is able to fill cells in a two-dimensional array by means of keyboard entries. Each cell contains a numerical value, a formula, or a label. A formula is an expression which may include constants, operations, functions, and references to the contents of other cells. A commonly used spreadsheet application is Lotus 1-2-3; another is Excel published by Microsoft. Lotus also permits cells to contain "macros", expressions which contain programming steps interpreted by the Lotus application.
Most database applications have the drawback that programming is quite difficult. It is estimated that well over nine-tenths of the users of dBase, for example, never write any programs in the native dBase programming language, but only perform menu-driven activities such as the aforementioned editing and browsing. Among the many factors making programming difficult are the cumbersome requirements for creating and debugging database programs. The user must typically type in the entirety of a programming line, including the operations, field names, constants, etc. that make up a valid line of program. Syntax and data type mismatch errors are commonplace and cumbersome. Numerical operations such as summing and subtotaling are not easy, nor are operations intended to apply across all records, e.g. to increase the contents of a selected field by a percentage.
With many database applications, one who would write a program is constantly faced with having to try to keep in mind the names of fields in each database that is to be used. If two database files are to be accessed at once, the user will often have the additional complication of a field name turning up in more than one database file, which must also be kept in mind. The user will enter one or more program lines and then execute them. At execution time the user may learn, for the first time, that fields of inconsistent data type are being added, that there is an error in the syntax of the entered lines, or that a nonexistent field has been referred to. The user is thus subjected to a debugging cycle of numerous iterations just to obtain a runnable program, and only after that is the user in a position to check the program for logical correctness and finishing touches.
Most spreadsheet applications are also difficult to program. As with database applications, it is estimated that well over nine-tenths of users of Lotus never create macros to solve problems, but only enter the aforementioned labels, formulas, and constants. While many spreadsheet applications including Lotus have commands and menu choices (including sorting and looking up) intended to permit use of the spreadsheet for database purposes, most users have trouble using them.
When conventional database or spreadsheet applications are employed, a not infrequently encountered task is selecting records for further processing and analysis. In dBase, for example, one does what is called setting a "filter" to cause the display and processing only of records meeting a criterion.
An example will show how selecting records is inconvenient and problematical with present-day applications. A user may be working with a database file having a field for a Zip code, and may wish to arrive at statistics based on the records having a particular Zip code. The user will write a program in which, at the outset a selection step is performed setting a filter so that only records having the particular Zip code are considered for further computation. After executing the program, the user will obtain the desired data, but only, of course, after the usual iterative process of revising the program. After the program is debugged, there could be the unhappy news that there are no records matching the desired Zip code.
It would be helpful to many users of database and spreadsheet applications if there were a way to generate program steps permitting the user to give more attention to the step being generated and less to pesky aspects such as (1) having to remember the fields which can be used in the step, and (2) having to get the syntax and data types right on the first try. It would be additionally helpful to such users if there were a way to find out, and the time of generating the code relating to selecting records, the universe of actual values in the fields being used in the selection process.